<?php defined('SYSPATH') or die('No direct script access.');

class Genes_Model extends Model {

	public function __construct() {		
		$this->db = Database::instance();
	}
	
	public function count_genes()
    { 
		$query = $this->db->select('id')->get('genes');
		return $query->count();
    }

	public function count_gene_members($id)
    { 
    	$sql = '
			SELECT count(*) as total
			FROM genes_profiles gp INNER JOIN profiles p ON gp.profile_id=p.id
			WHERE p.expire_date >= CURDATE() 
			AND p.status=1 
			AND gp.gene_id=?    	
    	';
    	
		$result = $this->db->query($sql, array($id))->current()->total;
		return $result;
    }

    public function browse_gene_members($id, $limit, $offset)
    {
    	$sql = '
			SELECT gp.gene_id, 
			p.id, 
			p.prefix_id, 
			(SELECT title FROM masters WHERE type_id=2 AND id=p.prefix_id) AS prefix,
			p.firstname, 
			p.lastname,
			p.member_type as member_type_id,
			(SELECT title FROM masters WHERE type_id=5 AND id=p.member_type) AS member_type
			FROM genes_profiles gp INNER JOIN profiles p ON gp.profile_id=p.id
			WHERE p.expire_date >= CURDATE() 
			AND p.status=1 
			AND gp.gene_id=?
			ORDER BY p.firstname, p.lastname
			LIMIT ?, ?;
    	';
		$result = $this->db->query($sql, array($id, $offset, $limit));
		return $result;
    }
    
    public function browse($limit, $offset)
    {
    	$sql = '
			SELECT * 
			FROM genes 
			ORDER BY name
			LIMIT ?, ?;
    	';
		$result = $this->db->query($sql, array($offset, $limit));
		return $result;
    }
    
    public function loads() {
    	$sql = '
			SELECT * 
			FROM genes 
			ORDER BY name;
    	';
		$query = $this->db->query($sql);
		return $query->result(FALSE);
    }
    
    public function load($id) {
    	$sql = '
			SELECT * 
			FROM genes 
			WHERE id=?;
    	';
		$query = $this->db->query($sql, array($id));
		return $query->result(FALSE);
    }
        
    public function save($data, $mode='add') {
		$this->db->query('BEGIN');
		try {
			if ($mode=='add') {
				$sql = 'INSERT INTO genes(name, description, created) VALUES(?,?,CURRENT_TIMESTAMP())';
				$query = $this->db->query($sql, array($data['name'], $data['description']));
			}
			else {
				$sql = 'UPDATE genes SET name=?, description=?, updated=CURRENT_TIMESTAMP() WHERE id=?;';
				$query = $this->db->query($sql, array($data['name'], $data['description'], $data['id']));				
			}
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
    }
    
    public function delete($id) {
 		$this->db->query('BEGIN');
		try {			
			$sql = 'DELETE FROM genes WHERE id=?;';
			
			$query = $this->db->query($sql, array($id));
			
			$this->db->query('COMMIT');
			
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	   	
    }
}